# @file    : preserve_style_test
# @time    : 2025/4/17
# @author  : yongpeng.yao
# @desc    :
import time
from copy import copy

from openpyxl import load_workbook

from baidu_trans_text import baidu_translate_text


def translate_excel_preserve_format(input_path, output_path, src_lang='jp', dest_lang='zh'):
    """
    翻译Excel内容并保留所有原始格式
    :param input_path: 输入文件路径
    :param output_path: 输出文件路径
    :param src_lang: 源语言
    :param dest_lang: 目标语言(默认简体中文)
    """
    # 加载工作簿
    wb = load_workbook(input_path)

    # 遍历所有工作表
    for sheet in wb.worksheets:
        print(f"正在翻译工作表: {sheet.title}")

        # 遍历所有有内容的单元格
        for row in sheet.iter_rows():
            for cell in row:
                if cell.value and isinstance(cell.value, str):
                    try:
                        # 保留原始样式
                        original_style = {
                            'font': copy(cell.font),
                            'fill': copy(cell.fill),
                            'border': copy(cell.border),
                            'alignment': copy(cell.alignment),
                            'number_format': cell.number_format
                        }

                        # 翻译内容(添加延迟避免API限制)
                        time.sleep(0.001)  # 免费API建议添加延迟
                        translated = baidu_translate_text(cell.value, src_lang, dest_lang)

                        # 更新单元格值但保留样式
                        cell.value = translated

                        # 重新应用原始样式
                        cell.font = original_style['font']
                        cell.fill = original_style['fill']
                        cell.border = original_style['border']
                        cell.alignment = original_style['alignment']
                        cell.number_format = original_style['number_format']

                    except Exception as e:
                        print(f"翻译失败(单元格 {cell.coordinate}): {e}")
                        continue
        # 翻译表名
        sheet.title = baidu_translate_text(sheet.title, src_lang, dest_lang)
    # 保存工作簿
    wb.save(output_path)
    print(f"翻译完成! 文件已保存到: {output_path}")


if __name__ == '__main__':
    file_path = './副本ヒアリングシート2.xlsx'
    translate_excel_preserve_format(
        input_path=file_path,
        output_path='translated2.xlsx',
    )
